package com.zerowidth.db.op;

import com.zerowidth.db.DBHelper;
import com.zerowidth.db.DBConn;
import com.zerowidth.db.utils.IOUtils;
import com.zerowidth.db.utils.TimeUtils;

import java.sql.ResultSet;
import java.sql.SQLException;

public class SMS_DB {

    //注册时的验证码
    public static final int SMS_TYPE_REGISTER = 1000;
    //手机号+验证码登录
    public static final int SMS_TYPE_LOGIN = 1001;
    //找回密码时的验证码
    public static final int SMS_TYPE_FIND_PWD = 1003;


    public static int count(String start_time, String end_time) {
        String sql = String.format("SELECT COUNT(*) FROM sms_record WHERE send_time >= '%s' " +
                " AND send_time <= '%s'", start_time, end_time);

        return DBHelper.queryCount(sql);
    }

    /**
     * @return 此ip今天发送验证码的条数
     */
    public static int checkIpTodaySMSCount(String ip, String send_time) {
        String sql = String.format(
                "SELECT COUNT(*) FROM sms_record " +
                        "WHERE ip = '%s' AND send_time >= '%s'", ip, send_time);
        return DBHelper.queryCount(sql);
    }

    /**
     * @return 此手机号今天发送验证码的条数
     */
    public static int checkPhoneSMSCount(String phone_number, String start_time, String end_time) {
        String sql = String.format(
                "SELECT COUNT(*) FROM sms_record WHERE phone_number = '%s' " +
                        " AND send_time >= '%s' AND send_time <= '%s'",
                phone_number, start_time, end_time);
        return DBHelper.queryCount(sql);
    }

    /**
     * 检查此号码1分钟内是否已经发送过 type业务类型的验证码
     *
     * @return true:合法； false:不合法，1分钟内多次请求
     */
    public static boolean checkSMSCodeIntervalValid(String phone, int type) {
        String sql = String.format(
                "SELECT * FROM sms_record " +
                        " WHERE phone_number = '%s' AND sms_type = %d AND sms_status = 0 " +
                        " order by send_time desc LIMIT 1", phone, type);

        ResultSet rs = null;
        try {
            rs = DBConn.getInst().getStmt().executeQuery(sql);

            if (rs.next()) {
                String timestamp = rs.getString("send_time");

                long timestampLong = TimeUtils.convert(timestamp, TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

                if (System.currentTimeMillis() - timestampLong >= 60 * 1000) {
                    return true;
                } else {
                    return false;
                }
            } else {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(rs);
        }

        return false;
    }

    public static boolean insertSMS(String phone, String code, int sms_type, String ip,
                                    String signName, String templateCode, boolean realSend) {

        String time = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);
        String sql = String.format("INSERT INTO sms_record (phone_number, code, send_time, sms_type, templateCode, sign_name, real_send, ip) " +
                        "VALUES ('%s', '%s', '%s', %d, '%s', '%s', %d, '%s')",
                phone, code, time, sms_type, templateCode, signName, realSend ? 1 : 0, ip);

        return DBHelper.executeUpdate(sql) > 0;
    }

    public static boolean checkAuthCodeLogin(String phone, String code) {
        String sql = String.format("SELECT * FROM sms_record " +
                        "WHERE phone_number = '%s' and code = '%s' " +
                        " AND sms_type = %d AND sms_status = 0 order by send_time desc LIMIT 1",
                phone, code, SMS_TYPE_LOGIN);

        ResultSet rs = null;
        try {
            rs = DBConn.getInst().getStmt().executeQuery(sql);

            if (rs.next()) {
                String send_time = rs.getString("send_time");
                int valid_duration = rs.getInt("valid_duration");
                if (valid_duration <= 0) {
                    valid_duration = 300000; //默认5分钟有效期
                }

                long sendTimeL = TimeUtils.convert(send_time, TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

                if (System.currentTimeMillis() - sendTimeL >= valid_duration) {
                    //超过有效期
                    return false;
                } else {
                    //未超过有效期
                    return true;
                }
            } else {
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(rs);
        }

        return false;
    }

    public static int disableAuthCodeLoginRecord(String phone, int type) {
        String sql = String.format("UPDATE sms_record SET sms_status = 1" +
                        " WHERE phone_number = '%s' and sms_type = %d",
                phone, type);

        return DBHelper.executeUpdate(sql);
    }

}
